package cn.stylefeng.guns.sys.modular.system.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.core.listener.BtySfcListener;
import cn.stylefeng.guns.sys.core.util.DateUtil;
import cn.stylefeng.guns.sys.core.util.JsonResult;
import cn.stylefeng.guns.sys.core.util.UUIDUtils;
import cn.stylefeng.guns.sys.modular.system.entity.BtyMdCaiwu;
import cn.stylefeng.guns.sys.modular.system.entity.BtyMdPsfymx;
import cn.stylefeng.guns.sys.modular.system.entity.BtyMdSfc;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyBrandMapper;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyMdCaiwuMapper;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyMdPsfymxMapper;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyMdSfcMapper;
import cn.stylefeng.guns.sys.modular.system.model.params.*;
import cn.stylefeng.guns.sys.modular.system.model.result.BtyMdCaiwuResult;
import cn.stylefeng.guns.sys.modular.system.model.result.BtySfcDto;
import cn.stylefeng.guns.sys.modular.system.service.*;
import cn.stylefeng.roses.core.reqres.response.ErrorResponseData;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;

import static java.util.stream.Collectors.toList;

/**
 * <p>
 * 门店财务主表 服务实现类
 * </p>
 *
 * @author jiangge
 * @since 2021-09-08
 */
@Service
public class BtyMdCaiwuServiceImpl extends ServiceImpl<BtyMdCaiwuMapper, BtyMdCaiwu> implements BtyMdCaiwuService {
    @Autowired
    private BtyMdZichanfzbService btyMdZichanfzbService;

    @Autowired
    private BtyMdLirunbiaoService btyMdLirunbiaoService;

    @Autowired
    private BtyMdLirunbiaozgService btyMdLirunbiaozgService;

    @Autowired
    private BtyBrandMapper btyBrandMapper;
    @Autowired
    private BtyMdPsfymxService btyMdPsfymxService;
    @Autowired
    private BtyMdSfcService btyMdSfcService;
    @Autowired
    private BtyMdSfcMapper btyMdSfcMapper;
    @Autowired
    private BtyMdPsfymxMapper btyMdPsfymxMapper;

    @Autowired
    private BtyMdCaiwuMapper btyMdCaiwuMapper;

    @Override
    public void add(BtyMdCaiwuParam param) {
        BtyMdCaiwu entity = getEntity(param);
        this.saveOrUpdate(entity);
    }

    @Override
    public void delete(BtyMdCaiwuParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(BtyMdCaiwuParam param) {
        BtyMdCaiwu oldEntity = getOldEntity(param);
        BtyMdCaiwu newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public BtyMdCaiwuResult findBySpec(BtyMdCaiwuParam param) {
        return null;
    }

    @Override
    public List<BtyMdCaiwuResult> findListBySpec(BtyMdCaiwuParam param) {
        return null;
    }

    @Override
    public LayuiPageInfo findPageBySpec(BtyMdCaiwuParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    /**
     * 导入资产负债表
     *
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importZcfzb(MultipartFile file) throws Exception {
        //文件名
        String fileName = file.getOriginalFilename();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }

        //excel中多个sheet表，获取所有sheet表的数量
        //int sheets = wb.getNumberOfSheets();
        //TODO-1 默认就一个sheet
        Sheet sheet = wb.getSheetAt(0);
        //TODO-2 第一行第一列是资产负债表
        Row row1 = sheet.getRow(0);
        Cell row1Cell1 = row1.getCell(0);
        String tableName = row1Cell1.getStringCellValue();

        if (StringUtils.isNotBlank(tableName) && !tableName.trim().equals("资产负债表")) {
            return ErrorResponseData.error("导入失败，第一行一列内容与《资产负债表》规定格式不符");
        }

        //TODO-3 获取会计月
        Row row2 = sheet.getRow(1);
        Cell row2Cell2 = row2.getCell(1);
        String row2Cell2Val = row2Cell2.getStringCellValue();
        String month = row2Cell2Val.substring(row2Cell2Val.length() - 7, row2Cell2Val.length());
        Long monthNum = Long.valueOf(month.replace("-", "").replace(" ", ""));


        //TODO-4 获取第三行除妖码、门店名以外的列名称
        Map<Integer, String> columnMap = new HashMap<>();
        //循环单元格cell
        for (int cellNum = 0; cellNum <= sheet.getRow(2).getLastCellNum(); cellNum++) {
            Cell cell = sheet.getRow(2).getCell(cellNum);
            //把单元格的内容转换为字符串
            if (cell != null) {
                cell.setCellType(CellType.STRING);
                String stringCellValue = cell.getStringCellValue();
                columnMap.put(cellNum, stringCellValue.trim());
            }
        }

        //TODO-5 获取第四行以下的门店及字段和内容
        List<BtyMdCaiwuParam> shopList = new ArrayList<>();
        List<BtyMdZichanfzbParam> zcczbList = new ArrayList<>();
        for (int r = 3; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            String yaoma = rmoveZeroAndDot(getStringValue(row.getCell(0)));
            if (yaoma.equals("")) {
                continue;
            }
            String shopName = row.getCell(1).getStringCellValue().trim();
            BtyMdCaiwuParam btyMdCaiwuParam = new BtyMdCaiwuParam();
            Long btyMdCaiwuId = Long.valueOf(monthNum + yaoma);
            btyMdCaiwuParam.setBtyMdCaiwuId(btyMdCaiwuId);
            btyMdCaiwuParam.setYaoma(yaoma);
            btyMdCaiwuParam.setDm(shopName);
            if(!StringUtils.isBlank(shopName)&&shopName.contains("店")&&yaoma.length()!=7){
                return ErrorResponseData.error("导入失败，第"+(r+1)+"行,门店："+shopName+"的妖码不是7位，请确认正确后再次上传");
            }

            btyMdCaiwuParam.setKjqj(month);
            btyMdCaiwuParam.setFlagZcfzb(1);
            shopList.add(btyMdCaiwuParam);

            //循环单元格cell
            for (int cellNum = 2; cellNum <= row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                //把单元格的内容转换为字符串
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = getStringValue(cell);
                    BtyMdZichanfzbParam zichanfzbParam = new BtyMdZichanfzbParam();
                    zichanfzbParam.setBtyMdZichanfzbId(UUIDUtils.getId());
                    zichanfzbParam.setBtyMdCaiwuId(btyMdCaiwuId);
                    zichanfzbParam.setSx(cellNum - 1);
                    zichanfzbParam.setKemu(columnMap.get(cellNum));
                    zichanfzbParam.setKemuVal(new BigDecimal((stringCellValue == null || stringCellValue.equals("")) ? "0" : stringCellValue).setScale(4));
                    zichanfzbParam.setCreateTime(new Date());
                    zcczbList.add(zichanfzbParam);
                }
            }

        }

        // TODO-5 保存之前删除之前存入的当月数据
        this.baseMapper.deleteCaiWuAndZcfzbData(month,monthNum);

        //TODO-6 写入数据
        if (CollectionUtil.isNotEmpty(shopList)) {
            for (BtyMdCaiwuParam shop : shopList) {
                this.add(shop);
            }
        }
        if (CollectionUtil.isNotEmpty(zcczbList)) {
//            for (BtyMdZichanfzbParam data : zcczbList) {
//                btyMdZichanfzbService.add(data);
//            }

            //每150条保存一次
            for(int i = 0; i < zcczbList.size();){
                int i2 = (i + 260);
                i2 = i2 > zcczbList.size() ? zcczbList.size() : i2;
                List<BtyMdZichanfzbParam> sub = zcczbList.subList(i, i2);
                //将这些数据改为通过状态
                btyMdZichanfzbService.addList(sub);
                i = i2;
            }
        }

        //TODO-7 删除主表中没有资产负债表数据和利润表数据的主表数据
        this.baseMapper.deleteCaiWuData(month);

        return ResponseData.success();
    }

    /**
     * 导入损益表
     *
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importSyb(MultipartFile file) throws Exception {
            //文件名
            String fileName = file.getOriginalFilename();

            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                throw new Exception("上传文件格式不正确");
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }

            InputStream is = file.getInputStream();
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }

            //excel中多个sheet表，获取所有sheet表的数量
            //int sheets = wb.getNumberOfSheets();
            //TODO-1 默认就一个sheet
            Sheet sheet = wb.getSheetAt(0);
            //TODO-2 第一行第一列是资产负债表
            Row row1 = sheet.getRow(0);
            Cell row1Cell1 = row1.getCell(0);
            String tableName = row1Cell1.getStringCellValue();

            if (StringUtils.isNotBlank(tableName) && !tableName.trim().equals("利润表")) {
                return ErrorResponseData.error("导入失败，第一行一列内容与《利润表》规定格式不符");
            }

            //TODO-3 获取会计月
            Row row2 = sheet.getRow(1);
            Cell row2Cell2 = row2.getCell(1);
            String row2Cell2Val = row2Cell2.getStringCellValue();
            String month = row2Cell2Val.substring(row2Cell2Val.length() - 7, row2Cell2Val.length());
            Long monthNum = Long.valueOf(month.replace("-", "").replace(" ", ""));

            //TODO-4 获取第三行除妖码、门店名以外的列名称
            Map<Integer, String> columnMap = new HashMap<>();
            //循环单元格cell
            for (int cellNum = 0; cellNum <= sheet.getRow(2).getLastCellNum(); cellNum++) {
                Cell cell = sheet.getRow(2).getCell(cellNum);
                //把单元格的内容转换为字符串
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = cell.getStringCellValue();
                    columnMap.put(cellNum, stringCellValue.trim());
                }
            }

            //TODO-5 获取第四行以下的门店及字段和内容
            List<BtyMdCaiwuParam> shopList = new ArrayList<>();
            List<BtyMdLirunbiaoParam> zcczbList = new ArrayList<>();
            for (int r = 3; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                String yaoma = rmoveZeroAndDot(getStringValue(row.getCell(0)));
                if (yaoma.equals("")) {
                    continue;
                }
                String shopName = row.getCell(1).getStringCellValue().trim();
                BtyMdCaiwuParam btyMdCaiwuParam = new BtyMdCaiwuParam();
                Long btyMdCaiwuId = Long.valueOf(monthNum + yaoma);
                btyMdCaiwuParam.setBtyMdCaiwuId(btyMdCaiwuId);
                btyMdCaiwuParam.setYaoma(yaoma);
                btyMdCaiwuParam.setDm(shopName);
                if(!StringUtils.isBlank(shopName)&&shopName.contains("店")&&yaoma.length()!=7){
                    return ErrorResponseData.error("导入失败，第"+(r+1)+"行,门店："+shopName+"的妖码不是7位，请确认正确后再次上传");
                }

                btyMdCaiwuParam.setKjqj(month);
                btyMdCaiwuParam.setFlagLrb(1);
                shopList.add(btyMdCaiwuParam);

                //循环单元格cell
                for (int cellNum = 2; cellNum <= row.getLastCellNum(); cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    //把单元格的内容转换为字符串
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String stringCellValue = getStringValue(cell);
                        BtyMdLirunbiaoParam zichanfzbParam = new BtyMdLirunbiaoParam();
                        zichanfzbParam.setBtyMdLirunbiaoId(UUIDUtils.getId());
                        zichanfzbParam.setBtyMdCaiwuId(btyMdCaiwuId);
                        zichanfzbParam.setSx(cellNum - 1);
                        zichanfzbParam.setKemu(columnMap.get(cellNum));
                        // 必须指定精度，否则插入时会四舍五入
                        zichanfzbParam.setKemuVal(new BigDecimal((stringCellValue == null || stringCellValue.equals("")) ? "0" : stringCellValue).setScale(4));
                        zichanfzbParam.setCreateTime(new Date());
                        zcczbList.add(zichanfzbParam);
                    }
                }
            }

            // TODO-5 保存之前删除之前存入的当月数据
            this.baseMapper.deleteCaiWuAndLirunData(month,monthNum);

            //TODO-6 写入数据
            if (CollectionUtil.isNotEmpty(shopList)) {
                for (BtyMdCaiwuParam shop : shopList) {
                    this.add(shop);
                }
            }
            if (CollectionUtil.isNotEmpty(zcczbList)) {
//                for (BtyMdLirunbiaoParam data : zcczbList) {
//                    btyMdLirunbiaoService.add(data);
//                }

                //每150条保存一次
                for(int i = 0; i < zcczbList.size();){
                    int i2 = (i + 260);
                    i2 = i2 > zcczbList.size() ? zcczbList.size() : i2;
                    List<BtyMdLirunbiaoParam> sub = zcczbList.subList(i, i2);
                    //将这些数据改为通过状态
                    btyMdLirunbiaoService.addList(sub);
                    i = i2;
                }
            }

        //TODO-7 删除主表中没有资产负债表数据和利润表数据的主表数据
        this.baseMapper.deleteCaiWuData(month);

            return ResponseData.success();
    }

    /**
     * 导入配送费用明细
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importPsfymx(MultipartFile file) throws Exception {
        //文件名
        String fileName = file.getOriginalFilename();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }

        //excel中多个sheet表，获取所有sheet表的数量
        //int sheets = wb.getNumberOfSheets();
        //TODO-1 默认就一个sheet
        Sheet sheet = wb.getSheetAt(0);

        //获取当前月的上个月份
        String lastMonth = DateUtil.getLastMonth();//yyyyMM
        String lastMonthStr = DateUtil.getLastMonthStr();//YYYY-MM
        List<Map<Object, Object>> list = new ArrayList<Map<Object, Object>>();

        //TODO-5 获取第四行以下字段和内容
        for (int r = 4; r <= sheet.getLastRowNum() - 1; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Map<Object, Object> map = new HashMap<>();

            map.put("sx",r+1);//行号

            //循环单元格cell
            for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                //把单元格的内容转换为字符串
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    map.put(cellNum, cell.getStringCellValue());
                }
            }

            list.add(map);
        }

        List<BtyMdCaiwuParam> shopList = new ArrayList<>();
        List<BtyMdPsfymxParam> psfyList = new ArrayList<>();

        //循环取出的数据
        for (Map map : list) {

            Integer sx = map.get("sx") == null ? null : Integer.valueOf(map.get("sx").toString());//行号

            String className = map.get(0) == null ? "" : map.get(0).toString();//存货分类名称
            String shopName = map.get(1) == null ? "" : map.get(1).toString();//部门
            String goodName = map.get(4) == null ? "" : map.get(4).toString();//存货名称
            String goodSpec = map.get(5) == null ? "" : map.get(5).toString();//存货规格
            String unit = map.get(6) == null ? "" : map.get(6).toString();//计量单位
            BigDecimal enterNum = map.get(7) == null ? BigDecimal.ZERO : new BigDecimal(map.get(7).toString());//进货数据
            BigDecimal returnNum = map.get(8) == null ? BigDecimal.ZERO : new BigDecimal(map.get(8).toString());//退货数量
            BigDecimal enterMoney = map.get(10) == null ? BigDecimal.ZERO : new BigDecimal(map.get(10).toString());//进货金额
            BigDecimal returnMoney = map.get(11) == null ? BigDecimal.ZERO : new BigDecimal(map.get(11).toString());//退货金额

            if (ToolUtil.isEmpty(shopName)){
                return ResponseData.error("第" + sx + "行数据没有店名！");
            }
            //查询该店妖码
            String yaoma = btyBrandMapper.getYmByName(shopName);
            if (ToolUtil.isEmpty(yaoma)){
                return ResponseData.error(shopName + "没有妖码！");
            }
            Long btyMdCaiwuId = Long.valueOf(lastMonth + yaoma);

            BtyMdCaiwuParam btyMdCaiwuParam = new BtyMdCaiwuParam();
            btyMdCaiwuParam.setBtyMdCaiwuId(btyMdCaiwuId);//财务id
            btyMdCaiwuParam.setYaoma(yaoma);//妖码
            btyMdCaiwuParam.setDm(shopName);//门店名
            btyMdCaiwuParam.setKjqj(lastMonthStr);//会计月
            btyMdCaiwuParam.setFlagPsfymx(1);//存在配送费用明细数据
            if(!shopList.contains(btyMdCaiwuParam)){
                shopList.add(btyMdCaiwuParam);
            }

            BtyMdPsfymxParam btyMdPsfymx = new BtyMdPsfymxParam();
            btyMdPsfymx.setSx(sx);//行号
            btyMdPsfymx.setGoodName(goodName);//存货名称
            btyMdPsfymx.setGoodSpec(goodSpec);//存货规格
            btyMdPsfymx.setShopName(shopName);//部门
            btyMdPsfymx.setClassName(className);//存货分类名称
            btyMdPsfymx.setEnterNum(enterNum);//进货数据
            btyMdPsfymx.setReturnNum(returnNum);//退货数量
            btyMdPsfymx.setEnterMoney(enterMoney);//进货金额
            btyMdPsfymx.setUnit(unit);//计量单位
            btyMdPsfymx.setReturnMoney(returnMoney);//退货金额
            btyMdPsfymx.setBtyMdCaiwuId(btyMdCaiwuId);//财务id

            psfyList.add(btyMdPsfymx);
        }

        // TODO-5 保存之前删除之前存入的当月数据
        this.baseMapper.deleteCaiWuAndPsmxData(lastMonthStr,lastMonth);

        //TODO-6 写入数据
        if (CollectionUtil.isNotEmpty(shopList)) {
            for (BtyMdCaiwuParam shop : shopList) {
                this.add(shop);
            }
        }
        if (CollectionUtil.isNotEmpty(psfyList)) {
            for (BtyMdPsfymxParam data : psfyList) {
                btyMdPsfymxService.add(data);
            }
        }

        //TODO-7 删除主表中没有资产负债表数据和利润表数据的主表数据
        this.baseMapper.deleteCaiWuData(lastMonthStr);

        return ResponseData.success();
    }

    /**
     * 导入收发存
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importSfc(MultipartFile file) throws Exception {
        //文件名
        String fileName = file.getOriginalFilename();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }

        //excel中多个sheet表，获取所有sheet表的数量
        //int sheets = wb.getNumberOfSheets();
        //TODO-1 默认就一个sheet
        Sheet sheet = wb.getSheetAt(0);

        //获取当前月的上个月份
        String lastMonth = DateUtil.getLastMonth();//yyyyMM
        String lastMonthStr = DateUtil.getLastMonthStr();//YYYY-MM
        List<Map<Object, Object>> list = new ArrayList<Map<Object, Object>>();

        //TODO-5 获取第四行以下的门店及字段和内容
        for (int r = 4; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Map<Object, Object> map = new HashMap<>();

            map.put("sx",r+1);//行号

            //循环单元格cell
            for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                //把单元格的内容转换为字符串
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    map.put(cellNum, cell.getStringCellValue());
                }
            }

            list.add(map);
        }

        List<BtyMdCaiwu> shopList = new ArrayList<>();
        List<BtyMdSfc> sfcList = new ArrayList<>();
        List<Long> cwIdList = new ArrayList<>();

        //循环取出的数据
        for (Map map : list) {

            Integer sx = map.get("sx") == null ? null : Integer.valueOf(map.get("sx").toString());//行号

            String yaoma = map.get(0) == null ? "" : map.get(0).toString();//妖码
            String shopName = map.get(1) == null ? "" : map.get(1).toString();//部门
            String className = map.get(5) == null ? "" : map.get(5).toString();//存货分类名称
            String goodName = map.get(7) == null ? "" : map.get(7).toString();//存货名称
            String goodSpec = map.get(8) == null ? "" : map.get(8).toString();//存货规格
            String unit = map.get(9) == null ? "" : map.get(9).toString();//计量单位
            BigDecimal qcNum = (map.get(10) == null || map.get(10) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(10).toString());//期初数量
            BigDecimal qcPrice = (map.get(11) == null || map.get(11) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(11).toString());//期初单价
            BigDecimal qcMoney = (map.get(12) == null || map.get(12) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(12).toString());//期初金额
            BigDecimal srNum = (map.get(13) == null || map.get(13) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(13).toString());//收入数量
            BigDecimal srPrice = (map.get(14) == null || map.get(14) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(14).toString());//收入单价
            BigDecimal srMoney = (map.get(15) == null || map.get(15) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(15).toString());//收入金额
            BigDecimal fcNum = (map.get(16) == null || map.get(16) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(16).toString());//发出数量
            BigDecimal fcPrice = (map.get(17) == null || map.get(17) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(17).toString());//发出单价
            BigDecimal fcMoney = (map.get(18) == null || map.get(18) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(18).toString());//发出金额
            BigDecimal jcNum = (map.get(19) == null || map.get(19) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(19).toString());//结存数量
            BigDecimal jcPrice = (map.get(20) == null || map.get(20) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(20).toString());//结存单价
            BigDecimal jcMoney = (map.get(21) == null || map.get(21) == "") ? BigDecimal.ZERO : new BigDecimal(map.get(21).toString());//结存金额

            if (ToolUtil.isEmpty(shopName)){
                return ResponseData.error("第" + sx + "行数据没有店名！");
            }
//            //查询该店妖码
//            String yaoma = btyBrandMapper.getYmByName(shopName);
//            if (ToolUtil.isEmpty(yaoma)){
//                return ResponseData.error(shopName + "没有妖码！");
//            }
            if (yaoma.contains(".")){
                yaoma = yaoma.substring(0, 7);
            }
            Long btyMdCaiwuId = Long.valueOf(lastMonth + yaoma);

            BtyMdCaiwu btyMdCaiwu = new BtyMdCaiwu();
            btyMdCaiwu.setBtyMdCaiwuId(btyMdCaiwuId);//财务id
            btyMdCaiwu.setYaoma(yaoma);//妖码
            btyMdCaiwu.setDm(shopName);//门店名
            btyMdCaiwu.setKjqj(lastMonthStr);//会计月
            btyMdCaiwu.setFlagSfc(1);//存在收发存数据
            if(!shopList.contains(btyMdCaiwu)){
                shopList.add(btyMdCaiwu);
                cwIdList.add(btyMdCaiwuId);
            }

            BtyMdSfc btyMdSfc = new BtyMdSfc();
            btyMdSfc.setSx(sx);//行号
            btyMdSfc.setGoodName(goodName);//存货名称
            btyMdSfc.setGoodSpec(goodSpec);//存货规格
            btyMdSfc.setShopName(shopName);//部门
            btyMdSfc.setClassName(className);//存货分类名称
            btyMdSfc.setUnit(unit);//计量单位
            btyMdSfc.setQcNum(qcNum);//期初数量
            btyMdSfc.setQcPrice(qcPrice);//期初单价
            btyMdSfc.setQcMoney(qcMoney);//期初金额
            btyMdSfc.setSrNum(srNum);//收入数量
            btyMdSfc.setSrPrice(srPrice);//收入单价
            btyMdSfc.setSrMoney(srMoney);//收入金额
            btyMdSfc.setFcNum(fcNum);//发出数量
            btyMdSfc.setFcPrice(fcPrice);//发出单价
            btyMdSfc.setFcMoney(fcMoney);//发出金额
            btyMdSfc.setJcNum(jcNum);//结存数量
            btyMdSfc.setJcPrice(jcPrice);//结存单价
            btyMdSfc.setJcMoney(jcMoney);//结存金额
            btyMdSfc.setBtyMdCaiwuId(btyMdCaiwuId);//财务id

            sfcList.add(btyMdSfc);
        }

        // TODO-5 保存之前删除之前存入的当月数据
        this.baseMapper.deleteCaiWuAndSfcData(lastMonthStr,lastMonth);

        //TODO-6 写入数据
        if (CollectionUtil.isNotEmpty(shopList)) {
            //财务表中是否存在
            List<BtyMdCaiwu> list1 = baseMapper.getCw(cwIdList);

            if (ToolUtil.isNotEmpty(list1)){
                //批量修改财务表中存在的数据
                baseMapper.updateCw(list1);

                List<BtyMdCaiwu> list2 = new ArrayList<>();

                //获取财务表中没有的数据
                shopList.forEach(btyMdCaiwu -> {

                    if (list1.stream().filter(w->w.getBtyMdCaiwuId().equals(btyMdCaiwu.getBtyMdCaiwuId())).findAny().isPresent()) {
                        list2.add(btyMdCaiwu);
                    }
//                        list1.forEach(caiwu ->{
//                        if(btyMdCaiwu.getBtyMdCaiwuId() != caiwu.getBtyMdCaiwuId()){
//                            list2.add(btyMdCaiwu);
//                        }
//                    });
                });
                // 差集 (shopList - list2)
                List<BtyMdCaiwu> list3 = shopList.stream().filter(item -> !list2.contains(item)).collect(toList());

                //每100条保存一次
                for(int i = 0; i < list3.size();){
                    int i2 = (i + 60);
                    i2 = i2 > list3.size() ? list3.size() : i2;
                    List<BtyMdCaiwu> sub = list3.subList(i, i2);
                    baseMapper.insertCw(sub);
                    i = i2;
                }
            }else {
                //每100条保存一次
                for(int i = 0; i < shopList.size();){
                    int i2 = (i + 60);
                    i2 = i2 > shopList.size() ? shopList.size() : i2;
                    List<BtyMdCaiwu> sub = shopList.subList(i, i2);
                    baseMapper.insertCw(sub);
                    i = i2;
                }
            }


//            this.saveOrUpdateBatch(shopList);
//            for (BtyMdCaiwuParam shop : shopList) {
//                this.add(shop);
//            }
        }
        if (CollectionUtil.isNotEmpty(sfcList)) {

            //每100条保存一次
            for(int i = 0; i < sfcList.size();){
                int i2 = (i + 90);
                i2 = i2 > sfcList.size() ? sfcList.size() : i2;
                List<BtyMdSfc> sub = sfcList.subList(i, i2);
//                btyMdSfcMapper.insertSfc(sub);
                i = i2;
            }

//            btyMdSfcService.saveBatch(sfcList);
//            for (BtyMdSfcParam data : sfcList) {
//                btyMdSfcService.add(data);
//            }
        }

        //TODO-7 删除主表中没有资产负债表数据和利润表数据的主表数据
        this.baseMapper.deleteCaiWuData(lastMonthStr);

        return ResponseData.success();
    }

    /**
     * 收发存数据写入
     * @param
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveSfc(List<BtyMdSfc> sfcList, List<BtyMdCaiwu> shopList, List<Long> cwIdList) {

        //收发存表写入数据
//        btyMdSfcMapper.insertSfc(sfcList);

//        //财务表中是否存在
//        List<BtyMdCaiwu> list1 = baseMapper.getCw(cwIdList);
//
//        if (ToolUtil.isNotEmpty(list1)){//存在 则修改状态
//            //批量修改财务表中存在的数据
//            baseMapper.updateCw(list1);
//
//            List<BtyMdCaiwu> list2 = new ArrayList<>();
//
//            //获取财务表中没有的数据
//            shopList.forEach(btyMdCaiwu -> {
//
//                if (list1.stream().filter(w->w.getBtyMdCaiwuId().equals(btyMdCaiwu.getBtyMdCaiwuId())).findAny().isPresent()) {
//                    list2.add(btyMdCaiwu);
//                }
//
//            });
//            // 差集 (shopList - list2)
//            List<BtyMdCaiwu> list3 = shopList.stream().filter(item -> !list2.contains(item)).collect(toList());
//
//            //每100条保存一次
//            for(int i = 0; i < list3.size();){
//                int i2 = (i + 60);
//                i2 = i2 > list3.size() ? list3.size() : i2;
//                List<BtyMdCaiwu> sub = list3.subList(i, i2);
//                baseMapper.insertCw(sub);
//                i = i2;
//            }
//        }else {//不存在 则新增数据
//
//            //每100条保存一次
//            for(int i = 0; i < shopList.size();){
//                int i2 = (i + 60);
//                i2 = i2 > shopList.size() ? shopList.size() : i2;
//                List<BtyMdCaiwu> sub = shopList.subList(i, i2);
//                baseMapper.insertCw(sub);
//                i = i2;
//            }
//        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void savePsfymx(List<BtyMdPsfymx> psfyList, List<BtyMdCaiwu> shopList, List<Long> cwIdList) {

        //配送费用明细表写入数据
        btyMdPsfymxMapper.insertPsfymx(psfyList);
    }

    /**
     * 导入正规损益表
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importSybzg(MultipartFile file)  throws Exception  {
        //文件名
        String fileName = file.getOriginalFilename();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }

        //excel中多个sheet表，获取所有sheet表的数量
        //int sheets = wb.getNumberOfSheets();
        //TODO-1 默认就一个sheet
        Sheet sheet = wb.getSheetAt(0);
        //TODO-2 第一行第一列是资产负债表
        Row row1 = sheet.getRow(0);
        Cell row1Cell1 = row1.getCell(0);
        String tableName = row1Cell1.getStringCellValue();

        if (StringUtils.isNotBlank(tableName) && !tableName.trim().equals("正规利润表")) {
            return ErrorResponseData.error("导入失败，第一行一列内容与《正规利润表》规定格式不符");
        }

        //TODO-3 获取会计月
        Row row2 = sheet.getRow(1);
        Cell row2Cell2 = row2.getCell(1);
        String row2Cell2Val = row2Cell2.getStringCellValue();
        String month = row2Cell2Val.substring(row2Cell2Val.length() - 7, row2Cell2Val.length());
        Long monthNum = Long.valueOf(month.replace("-", "").replace(" ", ""));

        //TODO-4 获取第三行除妖码、门店名以外的列名称
        Map<Integer, String> columnMap = new HashMap<>();
        //循环单元格cell
        for (int cellNum = 0; cellNum <= sheet.getRow(2).getLastCellNum(); cellNum++) {
            Cell cell = sheet.getRow(2).getCell(cellNum);
            //把单元格的内容转换为字符串
            if (cell != null&&ToolUtil.isNotEmpty(cell.getStringCellValue())) {
                cell.setCellType(CellType.STRING);
                String stringCellValue = cell.getStringCellValue();
                columnMap.put(cellNum, stringCellValue.trim());
            }
        }

        //TODO-5 获取第四行以下的门店及字段和内容
        List<BtyMdCaiwuParam> shopList = new ArrayList<>();
        List<BtyMdLirunbiaoParam> zcczbList = new ArrayList<>();
        for (int r = 3; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            String yaoma = rmoveZeroAndDot(getStringValue(row.getCell(0)));
            if (yaoma.equals("")) {
                continue;
            }
            String shopName = row.getCell(1).getStringCellValue().trim();
            BtyMdCaiwuParam btyMdCaiwuParam = new BtyMdCaiwuParam();
            Long btyMdCaiwuId = Long.valueOf(monthNum + yaoma);
            btyMdCaiwuParam.setBtyMdCaiwuId(btyMdCaiwuId);
            btyMdCaiwuParam.setYaoma(yaoma);
            btyMdCaiwuParam.setDm(shopName);
            if(!StringUtils.isBlank(shopName)&&shopName.contains("店")&&yaoma.length()!=7){
                return ErrorResponseData.error("导入失败，第"+(r+1)+"行,门店："+shopName+"的妖码不是7位，请确认正确后再次上传");
            }

            btyMdCaiwuParam.setKjqj(month);
            btyMdCaiwuParam.setFlagLrbzg(1);
            shopList.add(btyMdCaiwuParam);

            //循环单元格cell
            for (int cellNum = 2; cellNum <= row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                //把单元格的内容转换为字符串
                if (cell != null&&ToolUtil.isNotEmpty(getStringValue(cell))) {
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = getStringValue(cell);
                    BtyMdLirunbiaoParam zichanfzbParam = new BtyMdLirunbiaoParam();
                    zichanfzbParam.setBtyMdLirunbiaoId(UUIDUtils.getId());
                    zichanfzbParam.setBtyMdCaiwuId(btyMdCaiwuId);
                    zichanfzbParam.setSx(cellNum - 1);
                    zichanfzbParam.setKemu(columnMap.get(cellNum));
                    zichanfzbParam.setKemuVal(new BigDecimal((stringCellValue == null || stringCellValue.equals("")) ? "0" : stringCellValue).setScale(4));
                    zcczbList.add(zichanfzbParam);
                }
            }
        }

        // TODO-5 保存之前删除之前存入的当月数据
        this.baseMapper.deleteCaiWuAndLirunzgData(month,monthNum);

        //TODO-6 写入数据
        if (CollectionUtil.isNotEmpty(shopList)) {
            for (BtyMdCaiwuParam shop : shopList) {
                this.add(shop);
            }
        }
        if (CollectionUtil.isNotEmpty(zcczbList)) {
//            for (BtyMdLirunbiaoParam data : zcczbList) {
//                btyMdLirunbiaozgService.add(data);
//            }
            //每150条保存一次
            for(int i = 0; i < zcczbList.size();){
                int i2 = (i + 260);
                i2 = i2 > zcczbList.size() ? zcczbList.size() : i2;
                List<BtyMdLirunbiaoParam> sub = zcczbList.subList(i, i2);
                //将这些数据改为通过状态
                btyMdLirunbiaozgService.addList(sub);
                i = i2;
            }
        }

        //TODO-7 删除主表中没有资产负债表数据和利润表数据的主表数据
        this.baseMapper.deleteCaiWuData(month);

        return ResponseData.success();
    }

    /**
     * 异步导入收发存数据
     * @param file
     * @return
     */
    @Override
    @Async
    public Boolean addUsersAsync(MultipartFile file) {
        JsonResult jsonResult = new JsonResult();

        //获取当前月的上个月份
        String lastMonth = DateUtil.getLastMonth();//yyyyMM
        String lastMonthStr = DateUtil.getLastMonthStr();//YYYY-MM
        //保存之前删除之前存入的当月数据
        this.btyMdCaiwuMapper.deleteCaiWuAndSfcData(lastMonthStr,lastMonth);

        try {
            BtySfcListener btySfcListener = new BtySfcListener(btyMdSfcService);
            EasyExcel.read(file.getInputStream(), BtySfcDto.class, btySfcListener).sheet(0).headRowNumber(4).doRead();

            //导入成功之后 删除主表中没有资产负债表数据和利润表数据的主表数据
            this.btyMdCaiwuMapper.deleteCaiWuData(lastMonthStr);

        }catch (Exception e) {
            e.printStackTrace();
            jsonResult.setState(0);
            jsonResult.setErrmsg(e.getMessage());
            return false;
        }

        return true;
    }


    /**
     * 异步导入收发存数据
     * @param fileGetInputStream
     * @return
     */
    @Override
    @Async
    public Boolean addUsersInputStreamAsync(InputStream fileGetInputStream) {
        JsonResult jsonResult = new JsonResult();

        //获取当前月的上个月份
        String lastMonth = DateUtil.getLastMonth();//yyyyMM
        String lastMonthStr = DateUtil.getLastMonthStr();//YYYY-MM
        //保存之前删除之前存入的当月数据
        this.btyMdCaiwuMapper.deleteCaiWuAndSfcData(lastMonthStr,lastMonth);

        try {
            BtySfcListener btySfcListener = new BtySfcListener(btyMdSfcService);
            EasyExcel.read(fileGetInputStream, BtySfcDto.class, btySfcListener).sheet(0).headRowNumber(4).doRead();

            //导入成功之后 删除主表中没有资产负债表数据和利润表数据的主表数据
            this.btyMdCaiwuMapper.deleteCaiWuData(lastMonthStr);

        }catch (Exception e) {
            e.printStackTrace();
            jsonResult.setState(0);
            jsonResult.setErrmsg(e.getMessage());
            return false;
        }

        return true;
    }


    private String rmoveZeroAndDot(String s) {
        if (s.isEmpty()) {
            return "";
        }
        if (s.indexOf(".") > 0) {
            s = s.replaceAll("0+?$", "");
            s = s.replaceAll("[.]$", "");
        }
        return s;
    }

    private String getStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            Double numericCellValue = cell.getNumericCellValue();
            return numericCellValue.toString();
        } else if (cellType == CellType.BLANK) {
            //空值型
            return "";
        } else {
            String str = cell.getStringCellValue();
            try {
                return str;
            } catch (NumberFormatException ex) {
            }
            return "解析失败";
        }
    }

    public static boolean isInteger(String str) {
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        return pattern.matcher(str).matches();
    }


    private Serializable getKey(BtyMdCaiwuParam param) {
        return param.getBtyMdCaiwuId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private BtyMdCaiwu getOldEntity(BtyMdCaiwuParam param) {
        return this.getById(getKey(param));
    }

    private BtyMdCaiwu getEntity(BtyMdCaiwuParam param) {
        BtyMdCaiwu entity = new BtyMdCaiwu();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

}
